package demo;


import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;


public class SelectJDBC {

    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        /**
         * mysql> select * from borrow_info where id in
         * (select id from book where category_id = (select id from category where name = "计算机"));
         */

        String sql = "select * from borrow_info where id in (select id from book where category_id = (select id from category where name = '计算机'));";
        PreparedStatement statement = connection.prepareStatement(sql);
      //  id | book_id | student_id | start_time          | end_time
        //返回一个临时表
        ResultSet resultSet = statement.executeQuery();
        //要查询这个临时表 我们可以把这个resultSet 当做一个 "迭代器" ->利用它来遍历这个临时表
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            int book_id = resultSet.getInt("book_id");
           int student_id = resultSet.getInt("student_id");
            Timestamp start_time = resultSet.getTimestamp("start_time");
            Timestamp end_time = resultSet.getTimestamp("end_time");
            System.out.println("id : "+ id + "book_id : " + book_id + "student_id : "
                    + student_id + "start_time : " + start_time + "end_time : " + end_time);
        }
        //释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }

}
